package com.revenco.bi.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.revenco.bi.dao.ChartSqlDao;
import com.revenco.bi.dao.TnPublicDemensionValueSelDao;
import com.revenco.bi.model.TnPublicDemensionValueSel;
import com.revenco.bi.model.userbean.AnychartDataBean;
import com.revenco.bi.model.userbean.CFieldsBean;
import com.revenco.bi.model.userbean.CQueryBean;
import com.revenco.bi.model.userbean.ShowSql;
import com.revenco.bi.model.userbean.XFieldsBean;
import com.revenco.bi.model.userbean.YFieldsBean;
import com.revenco.bi.model.userbean.YQueryBean;
import com.revenco.bi.service.ChartSqlService;
import com.revenco.bi.service.TnOthtableMaintableRelaService;
import com.revenco.bi.util.Bean2Json;
import com.revenco.bi.util.JsonBinder;

/**
 * 
 * @author zhaozz
 * 
 */
@Service
public class ChartSqlServiceImpl implements ChartSqlService {
	@Autowired
	private ChartSqlDao chartSqlDao;
	@Autowired
	private TnOthtableMaintableRelaService tnOthtableMaintableRelaService;
	@Autowired
	private TnPublicDemensionValueSelDao tnPublicDemensionValueSelDao;

	private JsonBinder binder = new JsonBinder();
	private ShowSql showSql = new ShowSql();
	private Bean2Json bean2Json = new Bean2Json();
	private ArrayList<String> yF;
	private ArrayList<String> tempYFCG;
	private ArrayList<String> tempYFCGList;
	private String xF;
	private String eds;
	private String oilTableName;
	// 维表表名
	private String demonTabelName;
	private String field;
	private String condition;
	private String gbx;
	private String obf;
	private Map<String, String> yField;
	private List<String> legendList;
	private String param;
	private String tempStr;
	private String conditionOfCG;

	/**
	 * 根据筛选条件、X轴字段、Y轴字段拼接sql 返回 Sql语句
	 */

	public String spliceSql(String CFields, String XFields, String YFields) {
		String fullSql = "";

		this.getSql(CFields, XFields, YFields);

		fullSql = bean2Json.sqlJson(showSql);
		return fullSql;
	}
	
	/**
	 * 跟y轴相关数据，获取油量表中最大，最小字段值
	 */
	public String getOilGaugeBeanSql(String YFields) {
		String sql = "";
		yF = new ArrayList<String>();
		YQueryBean yFields = binder.fromJson(YFields, YQueryBean.class);
		for (int i = 0; i < yFields.getFullSelect().length; i++) {
			YFieldsBean yb = yFields.getFullSelect()[i];
			yF.add(yb.getFun() + "(" + yb.getName() + ") ");
		}
		Iterator<String> it = yF.iterator();
		String yfStr = "";
		while (it.hasNext()) {
			yfStr+= it.next();
			if (it.hasNext()) {
				yfStr+= " , " ;
			}
		}
		eds = yFields.getFullSelect()[0].getTableName();
		sql = "SELECT greatest(" + yfStr + ") as maxCount, least(" + yfStr + ") as minCount from " + eds;
		return sql; 
	}

	/**
	 * @author LingJun
	 * @param CFields
	 * @param XFields
	 * @param YFields
	 * @return
	 */
	public ShowSql getSql(String CFields, String XFields, String YFields) {
		try {
			// 序列化
			XFieldsBean xFields = binder.fromJson(XFields, XFieldsBean.class);

			CQueryBean cFields = null;
			if (CFields.length() > 5) {
				cFields = binder.fromJson(CFields, CQueryBean.class);
			}
			YQueryBean yFields = binder.fromJson(YFields, YQueryBean.class);
			String sql = partSql2(cFields, xFields, yFields);
			String testResult = chartSqlDao.sqlTest(sql);
			showSql.setSql(sql);
			// 执行sql进行验证
			if ("true".equals(testResult)) {
				showSql.setSuccess(true);
				showSql.setMsg("Exe Success!");
			} else {
				showSql.setSuccess(false);
				showSql.setMsg(testResult);
			}
		} catch (Exception e) {
			showSql.setSuccess(false);
			showSql.setMsg(e.toString());
			System.out.println(e);
		}
		return showSql;
	}

	/**
	 * 组装sql片段
	 * 
	 * @param cFields
	 * @param xFields
	 * @param yFields
	 * @return
	 */
	public String partSql(CQueryBean cFields, XFieldsBean xFields,
			YQueryBean yFields) {
		List<TnPublicDemensionValueSel> tdvs = tnOthtableMaintableRelaService
				.getTnPublicDemensionValueSelByPvId(xFields.getColumnId());
		TnPublicDemensionValueSel tempObj = null;
		if (tdvs != null && tdvs.size() > 0) {
			tempObj = tdvs.get(0);
			demonTabelName = tempObj.getConfigUrl() + " tx";
			// 维度字段属性别名分别为 name 和 id
			xF = "tx." + tempObj.getTextColName() + " as name, tx."
					+ tempObj.getValueColName() + " as id";
			// 分组sql
			gbx = "tx." + tempObj.getTextColName() + ", tx."
					+ tempObj.getValueColName();
			// 关联字段
			field = " tx." + xFields.getId() + " = " + " ty." + xFields.getId();
		} else {
			// EDS相关数据表
			demonTabelName = "";
			xF = "ty." + xFields.getName() + " as name , ty." + xFields.getName() + " as id";
			gbx = "ty." + xFields.getName();
			// 关联字段
			field = " ty." + xFields.getId() + " = " + " ty." + xFields.getId();
		}
		// y轴sql 片段
		yF = new ArrayList<String>();
		yField = new HashMap<String, String>();
		legendList = new ArrayList<String>();
		// 排序
		obf = "";
		for (int i = 0; i < yFields.getFullSelect().length; i++) {
			YFieldsBean yb = yFields.getFullSelect()[i];
			yF.add(yb.getFun() + "(ty." + yb.getName() + ") ");
			yField.put(yb.getName(), yb.getFun() + "(ty." + yb.getName() + ") ");
			legendList.add(yb.getName());
			obf += yb.getFun() + "(ty." + yb.getName() + ") "
					+ yb.getOrderType();
			// 添加逗号分隔符
			if (i < yFields.getFullSelect().length - 1) {
				obf += ",";
			}
		}
		// REF 维度表
		eds = yFields.getFullSelect()[0].getTableName() + " ty";

		// 报表筛选 where 条件
		StringBuilder conditionStr = new StringBuilder();
		if (cFields != null) {
			CFieldsBean cBean = null;
			int cLength = cFields.getCONDITION().length;
			for (int i = 0; i < cLength; i++) {
				cBean = cFields.getCONDITION()[i];
				conditionStr.append(" and " + cBean.getName() + " "
						+ cBean.getOperator() + " " + cBean.getValue());
			}
		}
		this.condition = conditionStr.toString();

		return SqlModel();
	}

	/**
	 * sql模板
	 * 
	 * @return sql语句
	 */
	private String SqlModel() {
		// 组装统计字段
		StringBuilder y = new StringBuilder();
		Iterator<String> it = legendList.iterator();
		String legend;
		while (it.hasNext()) {
			legend = it.next();
			y.append(yField.get(legend) + " as " + legend);
			if (it.hasNext()) {
				y.append(",");
			}
		}
		/*
		 * Nutz Sql 对象 Sql sqlObj = Sqls.create("select " + xF + ", " +
		 * y.toString() + " from " + eds + " join " + ref + " on " + field +
		 * " where 1=1 " + condition + " GROUP BY " + gbx + " ORDER BY " + obf);
		 */
		// 维表连接语句
		String refInfo = "";
		if (demonTabelName != null && !"".equals(demonTabelName)) {
			refInfo = " join " + demonTabelName + " on " + field;
		} else {

		}
		String sqlStr = "select " + xF + ", " + y.toString() + " from " + eds
				+ refInfo + " where 1=1 " + condition + " GROUP BY " + gbx
				+ " ORDER BY " + obf;

		return sqlStr;
	}

	public Map<String, List<AnychartDataBean>> getSeriesData(String CFields,
			String XFields, String YFields) {
		String sql = this.getSql(CFields, XFields, YFields).getSql();

		return chartSqlDao.getSeriesData(sql, legendList);
	}
	
	public Map<String, Map<String, String>> getSeriesData2(String CFields,
			String XFields, String YFields) {
		String sql = this.getSql(CFields, XFields, YFields).getSql();

		return chartSqlDao.getSeriesData2(sql, legendList);
	}

	/***************** 传参数拼sql方法 ***********************/
	/**
	 * 组装sql片段
	 * 
	 * @param cFields
	 * @param xFields
	 * @param yFields
	 * @return
	 */
	private String partSql2(CQueryBean cFields, XFieldsBean xFields,
			YQueryBean yFields) {
		// 获取维度值
		List<TnPublicDemensionValueSel> demenValueList = tnOthtableMaintableRelaService
				.getTnPublicDemensionValueSelByPvId(xFields.getColumnId());
		TnPublicDemensionValueSel publicDemensionValueSel = null;
		if ("3".equals(xFields.getColType())
				|| "4".equals(xFields.getColType())) {
			// EDS相关数据表
			demonTabelName = "";
			xF = "ty." + xFields.getName() + " as name, ty."+ xFields.getName() + " as id";
			gbx = "ty." + xFields.getName();
			// 关联字段
			field = " ty." + xFields.getId() + " = " + " ty." + xFields.getId();
		} else if ("1".equals(xFields.getColType()) && demenValueList != null
				&& demenValueList.size() > 0) {
			publicDemensionValueSel = demenValueList.get(0);
			demonTabelName = " ( "+publicDemensionValueSel.getConfigUrl() + ") tx";
			// 维度字段属性别名分别为 name 和 id
			xF = "tx." + publicDemensionValueSel.getTextColName()
					+ " as name, tx."
					+ publicDemensionValueSel.getValueColName() + " as id";
			// 分组sql
			gbx = "tx." + publicDemensionValueSel.getTextColName() + ", tx."
					+ publicDemensionValueSel.getValueColName();
			// 关联字段
			field = " tx." + xFields.getId() + " = " + " ty." + xFields.getId();
		} else {
			// EDS相关数据表
			demonTabelName = "";
			xF = "ty." + xFields.getName() + " as name , ty." + xFields.getName() + " as id";
			gbx = "ty." + xFields.getName();
			// 关联字段
			field = " ty." + xFields.getId() + " = " + " ty." + xFields.getId();
		}
		// y轴sql 片段
		yF = new ArrayList<String>();
		tempYFCG = new ArrayList<String>();
		tempYFCGList = new ArrayList<String>();
		yField = new HashMap<String, String>();
		legendList = new ArrayList<String>();
//		String total = null;
		// 排序
		obf = "";
		for (int i = 0; i < yFields.getFullSelect().length; i++) {
			YFieldsBean yb = yFields.getFullSelect()[i];
			yF.add(yb.getFun() + "(ty." + yb.getName() + ") ") ; 
			tempYFCG.add(yb.getFun() + "(" + yb.getName() + ")  as "+ yb.getName());
			yField.put(yb.getName(), yb.getFun() + "(" + yb.getName() + ") "); // "ty."
			legendList.add(yb.getName());
			obf += yb.getName() + " " + yb.getOrderType();
			// 添加逗号分隔符
			if (i < yFields.getFullSelect().length - 1) {
				obf += ",";
			}
		}
		// REF 维度表
		eds = yFields.getFullSelect()[0].getTableName() + " ty";
		oilTableName = yFields.getFullSelect()[0].getTableName();

		// 报表筛选 where 条件
		StringBuilder conditionStr = new StringBuilder();
		StringBuilder conditionStrOfCG = new StringBuilder();
		StringBuilder paramStr = new StringBuilder();
		StringBuilder tempStr = new StringBuilder();
		if (cFields != null) {
			CFieldsBean cBean = null;
			int cLength = cFields.getCONDITION().length;
			for (int i = 0; i < cLength; i++) {
				cBean = cFields.getCONDITION()[i];
				if (cBean.getParamType().equals("formalPara")) {
					paramStr.append("ty." + cBean.getName() + " as "
							+ cBean.getName() + ",");
					tempStr.append("," + cBean.getName());
				} else {
					conditionStr.append(" and ty." + cBean.getName() + " "
							+ cBean.getOperator() + " " + cBean.getValue());
					conditionStrOfCG.append(" and " + cBean.getName() + " "
							+ cBean.getOperator() + " " + cBean.getValue());
				}
			}
		}
		
		//拼接获取油量表tempCount值
		Iterator<String> it = tempYFCG.iterator();
		String yfStr = "";
		while (it.hasNext()) {
			yfStr+= it.next();
			if (it.hasNext()) {
				yfStr+= ", " ;
			}
		}
		if (paramStr.length() == 0 ) {
			this.param = paramStr.toString();
		} else {
			this.param = "," + paramStr.substring(0,paramStr.length()-1).toString();
		}
		this.condition = conditionStr.toString();
		this.conditionOfCG = conditionStrOfCG.toString();
		this.tempStr = yfStr.toString();

		return SqlModel2(xFields);
	}

	/**
	 * sql模板
	 * 
	 * @return sql语句
	 */
	private String SqlModel2(XFieldsBean xFields) {
		// 组装统计字段
		StringBuilder y = new StringBuilder();
		StringBuffer tempy = new StringBuffer();
		Iterator<String> it = legendList.iterator();
		String legend;
		while (it.hasNext()) {
			legend = it.next();
			y.append(yField.get(legend) + " as " + legend);
			tempy.append("ty." + legend + " as " + legend);
			if (it.hasNext()) {
				y.append(",");
				tempy.append(",");
			}
		}
		
		//组装油量表查询子查询字段
		StringBuilder cStr = new StringBuilder();
		Iterator<String>  it2 = tempYFCGList.iterator();
		String tempCStr;
		while (it2.hasNext()) {
			tempCStr = it2.next();
			cStr.append(tempCStr);
			if (it2.hasNext()) {
				cStr.append(", ");
			}
		}
		// 维表连接语句
		String refInfo = "";
		if (demonTabelName != null && !"".equals(demonTabelName)) {
			refInfo = " join " + demonTabelName + " on " + field;
		} else {

		}
		String sqlStr = "";
			if (StringUtils.isEmpty(xFields.getColumnId())) {
				sqlStr = "select " + tempStr.toString() + " from " + oilTableName + " where 1=1 "+ conditionOfCG;
			} else {
				sqlStr = "select name, id," + y.toString() + " from  (select " + xF
					+ ", " + tempy.toString() + param + " from " + eds
					+ refInfo + " where 1=1 " + condition
					+ " )  GROUP BY name,id" + " ORDER BY " + obf;
			}
		
		return sqlStr;
	}

}
